PostgreSQL 服务器编程 递归查询（CTE)

1 背景知识

PostgreSQL 是一个功能强大的开源对象关系数据库系统，它支持递归查询，并允许用户通过递归公共表表达式（Recursive Common Table Expressions，简称 CTE）来查询数据。递归查询在处理具有层级或树状结构的数据时非常有用，例如组织结构、文件系统目录等。

1.1 什么是递归查询？

递归查询（CTE）是一种允许 SQL 查询通过自身进行迭代调用的技术，目的是为了能够查询到所有相关联的数据行。

1.2 递归公共表表达式（CTE）语法结构

递归查询由两部分组成：
第一部分：基线（Base Case）：这是递归的起点，定义了递归查询的初始条件。
第二部分：递归部分（Recursive Part）：在这部分中，查询会调用自身，用于获取基线之后的关联数据。

在 PostgreSQL 中，递归查询是通过CTE子查询实现的。以下是一个递归查询的基本语法：

WITH RECURSIVE cte_name AS (
  -- 基线
  SELECT ...
  FROM ...
  WHERE ...
  
  UNION ALL
  
  -- 递归部分
  SELECT ...
  FROM cte_name
  JOIN ...
  WHERE ...
)
SELECT * FROM cte_name;

2 递归查询使用

2.1 数据准备

假设我们有一个人员表 employees， id 为员工 ID ， manager_id 字段表示每个员工的直接经理。NULL 值表示该员工没有经理（即，他们是上级经理）。

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2),
(6, 'Frank', 3);

2.2 编写递归查询函数

现在，我们想要找到某个经理的所有下属（包括间接下属），我们可以使用 WITH RECURSIVE 子句来编写一个递归查询。

下面是一个 PL/pgSQL 函数的示例，该函数接受一个员工 ID 作为参数，并返回该经理的所有下属。

CREATE OR REPLACE FUNCTION get_all_subordinates(p_employee_id INT)
RETURNS TABLE(id INT, name VARCHAR(100)) AS $
BEGIN
    RETURN QUERY
    WITH RECURSIVE subordinates AS (
        -- 基础情况：员工本人
        SELECT e.id, e.name
        FROM employees e
        WHERE e.id = p_employee_id
        UNION ALL
        -- 递归情况：下属的员工
        SELECT e.id, e.name
        FROM employees e
        JOIN subordinates s ON e.manager_id = s.id
    )
    -- 选择所有下属
    SELECT * FROM subordinates;
END;
$ LANGUAGE plpgsql;

在这个函数中，我们使用了 WITH RECURSIVE 子句来定义一个名为 subordinates 的递归公共表表达式（CTE）。

递归公共表表达式（CTE）首先选择指定的员工（基线），然后递归地选择这些员工的下属（递归部分）。这个过程会一直找到最后一个下属员工为止。

2.3 调用函数并执行递归查询

接下来调用这个函数并获取某个员工的所有下属。

SELECT * FROM get_all_subordinates(1);

//屏幕输出：
 id |  name
----+---------
  1 | Alice
  2 | Bob
  3 | Charlie
  4 | David
  5 | Eve
  6 | Frank
(6 行记录)

这将返回Alice的所有下属，包括Bob、Charlie、David、Eve和Frank。

3 注意事项

3.1 防止无限递归

递归查询必须有一个明确的退出条件，否则会导致无限递归。所以请确保 WHERE 子句中设置的限制条件是有效的。

3.2 其他注意事项

1. 递归CTE不能自我包含，它们必须通过 UNION ALL 操作符递归地引用自身。
2. 递归查询可能对性能有较大影响，尤其是在处理大量数据时。确保有适当的索引和限制条件以优化查询性能。

4 小结 & FAQ

递归查询是 PostgreSQL 中处理层级数据和树状的强大工具。通过递归CTE的工作原理和正确设置基线及递归部分，用户可以有效地查询和分析具有层级结构的数据。然而，递归查询也可能带来性能问题，因此在实际应用中需要仔细设计和测试。